/**
 * 删除指定的组织结构,包括该节点下面的结构.
 * 张露兵 lubing.zhang@cybercom.com
 * 2012-2-27
 */
drop procedure if exists del_org;

delimiter $

create procedure del_org(in oid int)
begin 
	
	declare gid int default -1;
	declare pid int default -1;
	declare grid int default -1;
	declare otype varchar(10);
	
	declare done int default false;
	declare child_oid int default -1;
	declare cur1 cursor for 
		select id from org_tree ot 
		where ot.group_id in (
			select id from `group` g where g.parent_group_id = gid
		) 
		and ot.position_id = -1;
	declare continue handler for not found set done = true;
	
	-- 修改递归深度
	set max_sp_recursion_depth = 10;

	-- 判断当前的组织结构的类型
	select ot.group_id, ot.position_id, ot.grade_id into gid, pid, grid from org_tree ot where ot.id = oid;
	if gid != -1 and pid = -1 then
		set otype = 'GROUP';
	elseif gid != -1 and pid != -1 and grid = -1 then
		set otype = 'POSITION';
	elseif gid != -1 and pid != -1 and grid !=-1 then
		set otype = 'GRADE';
	else
		set otype = 'UNKOWN';
	end if;
	
	if otype = 'GROUP' then
		-- 找到子group
		open cur1;
		read_loop: loop
		    fetch cur1 into child_oid;
		    if done then
		      leave read_loop;
		    end if;
		    -- 递归调用,删除子group
		    call del_org(child_oid);
		  end loop;
		-- 删除group以及group下的position和grade
		delete from `org_tree` where group_id = gid;
		-- 删除基础表group的信息
		delete from `group` where id = gid;
	elseif otype = 'POSITION' then
		-- 删除position以及position下的grade
		delete from `org_tree` where group_id = gid and position_id = pid;
	elseif otype = 'GRADE' then
		-- 删除grade
		delete from `org_tree` where id = oid;
	else
		select 'NOTHING TO DELETE';
	end if;
	
end
$



DROP PROCEDURE IF EXISTS get_ki_tobe_developed;

DELIMITER $$

CREATE PROCEDURE get_ki_tobe_developed(IN USERID INT,IN EVALUATION_YEAR INT)
	BEGIN
		
	  declare TARGET_YEAR INT;
	  set TARGET_YEAR = EVALUATION_YEAR - 1;
	  
	  Create TEMPORARY table tmp_eki (
	  SELECT eki.ki_id,ifNull(eki.supervisor_evaluation_score,0) evaluation_score
	  from evaluation e inner join evaluation_knowledge_item eki on e.id = eki.evaluation_id
	  where e.user_id = USERID and year(e.time) = EVALUATION_YEAR
	  );
	 
	 Create TEMPORARY table tmp_tki (
	   SELECT dt.ki_id,ifNull(dt.target_score,0) target_score
	  from evaluation e inner join development_target dt on e.id = dt.evaluation_id
	  where e.user_id = USERID and year(e.time) = TARGET_YEAR
    );
    
    Create TEMPORARY table tmp_final (
    select tmp_eki.ki_id,tmp_eki.evaluation_score,ifNull(tmp_tki.target_score,0) target_score,
	 (tmp_eki.evaluation_score-ifNull(tmp_tki.target_score,0)) as delta 
	 from tmp_eki left join tmp_tki on tmp_eki.ki_id = tmp_eki.ki_id
	 );
	 
	 select * from tmp_final where delta<0;
	 drop table tmp_eki;
	 drop table tmp_tki; 
	 drop table tmp_final;  
  END$$

DELIMITER ;

CALL get_ki_tobe_developed(1,2011);



DROP PROCEDURE IF EXISTS get_self_evaluation_table_data;

DELIMITER $$

CREATE PROCEDURE get_self_evaluation_table_data(IN YEAR INT, IN USERID INT,CON INT)

	BEGIN
   DECLARE EVALUATIONID INT;
   DECLARE EVALUATIONID2 INT;
   
	SELECT ID into EVALUATIONID  FROM EVALUATION WHERE YEAR(time) = YEAR-1 AND USER_ID = USERID ;
	SELECT ID into EVALUATIONID2  FROM EVALUATION WHERE YEAR(time) = YEAR AND USER_ID = USERID ;
	
   /*
		CON 
		1 LAST TARGET
		2 ALL
		3 KI TO BE DEVELOPED
	*/
  
	IF CON = 1 THEN	
		Create TEMPORARY table tmp2 (
		   select dt.target_score,ki.id,ki.code,ki.name,ki.parent_id 
			from development_target dt inner join `knowledge_item` ki on dt.ki_id = ki.id
			where dt.evaluation_id = EVALUATIONID
		); 
	
		Create TEMPORARY table tmp3 (
		   select t2.target_score,t2.id ki_id,t2.code,t2.name,t2.parent_id ,eki.supervisor_evaluation_score last_score
			from tmp2 t2 left join `evaluation_knowledge_item` eki on t2.id = eki.ki_id and eki.evaluation_id = EVALUATIONID	
		); 
		
	   Create TEMPORARY table tmp4 (
			select EVALUATIONID last_evaluation_id,EVALUATIONID2 current_evaluation_id,eki.self_evaluation_score,eki.self_evaluation_time,eki.supervisor_evaluation_score,eki.supervisor_evaluation_time,eki.ki_id kii_id
			from evaluation_knowledge_item eki where eki.evaluation_id = EVALUATIONID2
		);
	
		/*select * from tmp3 t3 left join tmp4 t4 on t3.ki_id = t4.kii_id;*/
	select t3.*,t4.*,e.status from tmp3 t3 left join tmp4 t4 on t3.ki_id = t4.kii_id left join evaluation e on t4.current_evaluation_id = e.id;
		drop table tmp4;
		drop table tmp3;
		drop table tmp2;
	END IF;
 
	IF CON = 2 THEN
   	/* ALL KI */
		Create TEMPORARY table tmp1 (
			SELECT ki.id ki_id,ki.code,ki.name,ki.parent_id 
			from user u inner join `grade` gr on u.grade_id = gr.id
			inner join position p on gr.position_id = p.id
			inner join `group` g on p.group_id = g.id
			inner join `group_knowledge_item` gki on g.id = gki.group_id
			inner join knowledge_item ki on ki.id = gki.ki_id
			where u.id = USERID
		);
		/* LAST YEAR EVALUATION INFO */
		Create TEMPORARY table tmp2 (
			SELECT  dt.target_score , eki.supervisor_evaluation_score last_score,eki.supervisor_evaluation_time,eki.ki_id kii_id
			from evaluation e
			inner join `evaluation_knowledge_item` eki on e.id = eki.evaluation_id
			inner join `development_target` dt on e.id = dt.evaluation_id
			where e.id = EVALUATIONID
		);
		/* CURRENT YEAR EVALAUTION INFO */
			Create TEMPORARY table tmp3 (
			SELECT  dt.target_score ,eki.self_evaluation_score,eki.self_evaluation_time, eki.supervisor_evaluation_score,eki.supervisor_evaluation_time,eki.ki_id kiii_id,e.status
			from evaluation e
			inner join `evaluation_knowledge_item` eki on e.id = eki.evaluation_id
			inner join `development_target` dt on e.id = dt.evaluation_id
			where e.id = EVALUATIONID2
		);
		
		/*UNION*/
   	select * from tmp1 left join tmp2 on tmp1.ki_id = tmp2.kii_id 
   	left join tmp3 on tmp1.ki_id = tmp3.kiii_id;
   	
   	drop table tmp3;
		drop table tmp2;
		drop table tmp1;
   
 END IF; 
/*
 IF CON = 3 THEN
 END IF;
 */
    END$$

DELIMITER ;

CALL get_self_evaluation_table_data(2011,1,1);




ALTER TABLE `material`  ADD COLUMN `approve_time` TIMESTAMP NULL AFTER `upload_time`;
ALTER TABLE `course`  ADD COLUMN `approve_time` TIMESTAMP NULL AFTER `create_time`;

drop function if exists `need_supervisor_confirm`;
delimiter //

create function need_supervisor_confirm(eid int, supervisor int) returns int
begin

   	declare plan_count int default 0;
	declare current_user_id int default null;
	
	select user_id into current_user_id from `evaluation` e where e.id = eid;
	
	if ifnull(supervisor, -1) = current_user_id then
		return 1;
	end if; 
	
	select count(*) into plan_count from ( 
		select 
			   c.id res_id, 'course' res_type, dt.ki_id ci_id, c.title title, ic.close_time, c.owner 
		from improvement_course ic 
			   inner join development_target dt on dt.id = ic.development_target_id 
			   inner join evaluation e on e.id = dt.evaluation_id 
			   inner join course c on c.id = ic.course_id 
		where 
			(
				ifnull(ic.supervisor_confirm, 0) != 1 and ifnull(ic.employee_confirm, 0) = 1 and ifnull(ic.dispose_confirm, -1) = -1 
				or ic.`status` = 3
			)
			and e.id = eid 
		union 
		select 
			   m.id res_id, 'material' res_type, dt.ki_id ci_id, m.name title, im.close_time, m.owner 
		from improvement_material im 
			   inner join development_target dt on dt.id = im.development_target_id 
			   inner join evaluation e on e.id = dt.evaluation_id 
				inner join material m on m.id = im.material_id 
		where 
			(
				ifnull(im.supervisor_confirm, 0) != 1 and ifnull(im.employee_confirm, 0) = 1 and ifnull(im.dispose_confirm, -1) = -1
				or im.`status` = 3
			)
			and e.id = eid
		union 
		select 
			   m.id res_id, 'material' res_type, -1 ci_id, m.name title, er.close_time, m.owner 
		from extra_resource er 
			   inner join evaluation e on e.id = er.evaluation_id inner join material m on m.id = er.resource_id 
		where 
			(
				ifnull(er.supervisor_confirm, 0) != 1 and ifnull(er.employee_confirm, 0) = 1 and ifnull(er.dispose_confirm, -1) = -1
				or er.`status` = 3
			)
			and e.id = eid
		union 
		select 
			   c.id res_id, 'course' res_type, -1 ci_id, c.title title, er.close_time, c.owner 
		from extra_resource er 
			   inner join evaluation e on e.id = er.evaluation_id inner join course c on c.id = er.resource_id 
		where 
			(
				ifnull(er.supervisor_confirm, 0) != 1 and ifnull(er.employee_confirm, 0) = 1 and ifnull(er.dispose_confirm, -1) = -1
				or er.`status` = 3
			)
			and e.id = eid
		union 
		select 
			   pp.id res_id, 'private_plan' res_type, -1 ci_id, pp.title title, pp.time close_time, pp.owner 
		from private_plan pp 
			   inner join evaluation e on e.id = pp.evaluation_id 
		where 
			(
				ifnull(pp.supervisor_confirm, 0) != 1 and ifnull(pp.employee_confirm, 0) = 1 and ifnull(pp.dispose_confirm, -1) = -1
				or pp.`status` = 3
			)
			and e.id = eid
	) plan;
	
	return plan_count > 0;

end
//



create or replace package oss_pkg_device_edition is
    PROCEDURE p_purge_device_edition
    (
        pi_agentname   IN VARCHAR2,       -- 请求的终端UA数据
        po_adaptver    OUT NUMBER,        -- 返回终端对应UA的版本
        po_screensize  OUT NUMBER,        -- 返回的屏幕类型，0：未知，1：小屏  2：中屏；2：大屏 3：特大屏
        po_clienttype  OUT NUMBER,        -- 返回的终端类型，0：未知，1：手机  2： 平板， 3： PC
        po_outtype     OUT VARCHAR2,      -- 返回的终端信息：格式 名称=值，多个信息以；分割
        po_returnvalue OUT NUMBER         -- 返回终端适配查询的返回值,0:成功，1：查询不到；2：异常
    );
end oss_pkg_device_edition;
/
create or replace package body oss_pkg_device_edition is
    PROCEDURE p_purge_device_edition
    (
        pi_agentname   IN VARCHAR2,       -- 请求的终端UA数据
        po_adaptver    OUT NUMBER,        -- 返回终端对应UA的版本,0:未知，1：简版，2：炫版，3：触屏版，4：简洁版，6：标准版，66：酷版，8：IPAD版
        po_screensize  OUT NUMBER,        -- 返回的屏幕类型，0：未知，1：小屏  2：中屏；2：大屏 3：特大屏
        po_clienttype  OUT NUMBER,        -- 返回的终端类型，0：未知，1：手机  2： 平板， 3： PC
        po_outtype     OUT VARCHAR2,      -- 返回的终端信息：格式 名称=值，多个信息以；分割
        po_returnvalue OUT NUMBER         -- 返回终端适配查询的返回值,0:成功，1：查询不到；2：异常
    )
    IS
        lv_wapversion     oss_device_agent.wapversion%TYPE;
        lv_modelname      oss_device_agent.modelname%TYPE;
        lv_manufacturer   oss_device_agent.manufacturer%TYPE;
        lv_quartervga     oss_device_agent.quartervga%TYPE;
        lv_screensize     oss_device_agent.screensize%TYPE;
        lv_systemname     oss_device_agent.systemname%TYPE;
        lv_systemversion  oss_device_agent.systemversion%TYPE;
        lv_browsername    oss_device_agent.browsername%TYPE;
        lv_browserversion oss_device_agent.browserversion%TYPE;
        ln_devtype        oss_device_agent.devtype%TYPE;
        ln_isdo           oss_device_agent.isdo%TYPE;     
        -- 动态sql
        lv_where_sql      VARCHAR2(4000);
        lv_query_sql      VARCHAR2(4000);
        ln_count          NUMBER(10);
        ln_countnum       NUMBER(10);
        lv_screenwidth    VARCHAR2(32);
        lv_screenhigtth   VARCHAR2(32);
    BEGIN
        po_adaptver := 0;
        po_screensize := 0;
        po_returnvalue := 1;
        
        -- 查询在终端信息库保存的终端版本
        BEGIN
            SELECT t.wapversion,
                   t.modelname,
                   t.manufacturer,
                   t.quartervga,
                   t.screensize,
                   t.systemname,
                   t.systemversion,
                   t.browsername,
                   t.browserversion,
                   t.devtype,
                   t.pointtype,
                   t.isdo
            INTO   lv_wapversion,
                   lv_modelname,
                   lv_manufacturer,
                   lv_quartervga,
                   lv_screensize,
                   lv_systemname,
                   lv_systemversion,
                   lv_browsername,
                   lv_browserversion,
                   ln_devtype,
                   po_adaptver,
                   ln_isdo
            FROM  oss_device_agent t
            WHERE agentname = upper(pi_agentname)
            AND rownum<=1;
        EXCEPTION
            WHEN no_data_found THEN
                po_returnvalue:=2;
        END;
        
        -- 判断终端类型
        po_clienttype := nvl(ln_devtype,0);
        
        IF po_adaptver = 0 THEN
            FOR c IN (SELECT ruleid,
                             pointversion,
                             includekey,
                             excludekey,
                             screentype
                  FROM   oss_agent_point_rule
                  ORDER BY pointversion DESC) LOOP
                  
                  -- where sql 初始
                  lv_where_sql := '';
                  -- 拼接like 条件
                  FOR inc IN (SELECT REGEXP_SUBSTR (TRIM(c.includekey), '[^,]+', 1,rownum) AS column_value FROM DUAL
                                      CONNECT BY ROWNUM <=
                                      LENGTH (TRIM(c.includekey)) - LENGTH (REPLACE (TRIM(c.includekey), ',', '')) +1) LOOP
                      lv_where_sql := lv_where_sql || 'AND '||''''|| upper(pi_agentname) ||''''||' LIKE ''%' ||inc.column_value || '%''';
                  END LOOP;
                  
                  -- 对排除规则进行判断
                  IF c.excludekey IS NOT NULL THEN
                      -- 拼接not like 条件
                      FOR inc IN (SELECT REGEXP_SUBSTR (TRIM(c.excludekey), '[^,]+', 1,rownum) AS column_value FROM DUAL
                                         CONNECT BY ROWNUM <=
                                         LENGTH (TRIM(c.excludekey)) - LENGTH (REPLACE (TRIM(c.excludekey), ',', '')) +1) LOOP
                          lv_where_sql := lv_where_sql || 'AND '||''''|| upper(pi_agentname) ||''''||' NOT LIKE ''%' ||inc.column_value || '%''';
                      END LOOP;
                  END IF;
                  
                  lv_query_sql := 'SELECT count(1) ' 
                         || 'FROM   dual ' 
                         || 'WHERE  1 = 1 ' || lv_where_sql;
                  EXECUTE IMMEDIATE lv_query_sql INTO ln_count;
                  IF ln_count >0 THEN
                      po_adaptver := c.pointversion;
                      EXIT;
                  END IF;
            END LOOP;
        END IF;
        
        IF po_adaptver = 0 THEN
            po_adaptver := 2;
        END IF;
        
        -- 如果agent信息表中没有相关的agent信息，则执行插入操作
        IF po_returnvalue = 2 THEN
            INSERT INTO oss_device_agent
               (agentid,
                agentname,
                pointtype,
                isdo,
                extstring)
            VALUES(
                 seq_oss_device_agent.nextval,
                 upper(pi_agentname),
                 po_adaptver,
                 1,
                 to_char(SYSDATE,'yyyy-MM-dd hh24:mi:ss'));
            COMMIT;
        ELSE
            IF po_adaptver <> 0 AND ln_isdo = 0 THEN
                 UPDATE oss_device_agent a
                 SET    a.pointtype      = po_adaptver,
                        a.isdo           = 1,
                        a.extstring      = to_char(SYSDATE,'yyyy-MM-dd hh24:mi:ss'),
                        a.modifytime     = SYSDATE
                 WHERE  a.pointtype <> po_adaptver -- 已经更改为目前的指向版本的不再更新
                 AND    agentname = upper(pi_agentname);
                 COMMIT;
            END IF;
        END IF;
        
        -- 判断屏幕大小 
        BEGIN
            ln_countnum := instr(lv_quartervga,'*');
            IF ln_countnum > 0 THEN
               lv_screenwidth := substr(lv_quartervga,1,ln_countnum - 1);
               lv_screenhigtth := substr(lv_quartervga,ln_countnum + 1);
          
               IF to_number(lv_screenwidth) * to_number(lv_screenhigtth) < 176*220 THEN
                  po_screensize := 1; -- 小屏
               END IF;
          
              IF to_number(lv_screenwidth) * to_number(lv_screenhigtth)>= 176*220 AND 
                 to_number(lv_screenwidth) * to_number(lv_screenhigtth) < 320*480 THEN
                 po_screensize := 2;-- 中屏
              END IF;
          
              IF to_number(lv_screenwidth) * to_number(lv_screenhigtth) >= 320*480 AND 
                 to_number(lv_screenwidth) * to_number(lv_screenhigtth) < 960*640 THEN
                 po_screensize := 3; -- 大屏
              END IF;
          
              IF to_number(lv_screenwidth) * to_number(lv_screenhigtth) >= 960*640 THEN
                 po_screensize := 4; -- 特大
              END IF;
           END IF;
        EXCEPTION
            WHEN OTHERS THEN
                po_screensize := 0;
        END;
        
        -- 判断终端类型
        IF po_clienttype = 0 THEN
            IF po_adaptver = 8 THEN
                po_clienttype := 2; -- 平板
            ELSIF po_adaptver IN(4,6) THEN
                po_clienttype := 3; --PC
            ELSE
                po_clienttype := 1; -- 手机
            END IF;
        END IF;
        
        -- 组装返回的信息
        po_outtype := 'manufacturer='||lv_manufacturer
                    ||';modelname='||lv_modelname
                    ||';quartervga='||lv_quartervga
                    ||';screensize='||lv_screensize
                    ||';systemname='||lv_systemname
                    ||';systemversion='||lv_systemversion
                    ||';browsername='||lv_browsername
                    ||';browserversion='||lv_browserversion
                    ||';waptype='||lv_wapversion;
        po_returnvalue :=0;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            po_returnvalue := 2;
    END p_purge_device_edition;
       
END oss_pkg_device_edition;

